package Com.HupochuanAdmin.Dal.Tab;

import java.util.Hashtable;
import java.util.List;
import java.util.Vector;

import Com.Hupochuan.Dal.DBManager.DBManager;
import Com.Hupochuan.Vo.Movie;

public class MovieTab {
	/*
	 * 本类私有对象
	 */
	private DBManager<Movie> dbManager = new DBManager<Movie>(Movie.class);
	
	/*
	 * 单一实例
	 */
	private MovieTab(){}
	private static MovieTab movieTab = new MovieTab();
	public static MovieTab getObject(){
		return movieTab;
	}
	
	/*
	 * 添加数据
	 */
	public int appendData(String name,String byname,long directorId,long areaId,String releaseDate,String baikeUrl,String summary){
		//参数部分
		StringBuffer sqlBuff = new StringBuffer();
		Hashtable<String,Object> paraHash = new Hashtable<String,Object>();
		
		//sql语句部分
		sqlBuff.append(" insert into Movie set ");
		sqlBuff.append(" 	name=${name}, ");
		sqlBuff.append(" 	byname=${byname}, ");
		sqlBuff.append(" 	directorId=${directorId}, ");
		sqlBuff.append(" 	areaId=${areaId}, ");
		sqlBuff.append(" 	baikeUrl=${baikeUrl}, ");
		sqlBuff.append(" 	summary=${summary}, ");
		sqlBuff.append(" 	createTime=now(), ");
		sqlBuff.append(" 	updateTime=now(), ");
		
		//上映时间
		sqlBuff.append(" 	releaseDate= ");		
		if(releaseDate.equals("")){
			sqlBuff.append(" null ");
		}
		else{
			sqlBuff.append(" ${releaseDate} ");
		}
		
		//sql参数部分
		paraHash.put("name",name);
		paraHash.put("byname",byname);
		paraHash.put("directorId",directorId);
		paraHash.put("areaId",areaId);
		paraHash.put("baikeUrl",baikeUrl);
		paraHash.put("summary",summary);
		paraHash.put("releaseDate",releaseDate);
		
		//执行sql 并返回参数
		return dbManager.updateSql(sqlBuff.toString(),paraHash);
	}
	
	/*
	 * 根据ID 更新数据
	 */
	public int updateData(String logoPath,long id){
		//参数部分
		StringBuffer sqlBuff = new StringBuffer();
		Hashtable<String,Object> paraHash = new Hashtable<String,Object>();
		
		//sql语句部分
		sqlBuff.append(" update Movie set ");
		sqlBuff.append(" logoPath=${logoPath}, ");
		sqlBuff.append(" updateTime=now() ");
		sqlBuff.append(" where id=${id} ");
		
		//sql参数部分
		paraHash.put("logoPath",logoPath);
		paraHash.put("id",id);
		
		//执行sql 并返回参数
		return dbManager.updateSql(sqlBuff.toString(),paraHash);
	}
	
	/*
	 * 根据ID 更新数据
	 */
	public int updateData(String name,String byname,long directorId,long areaId,String releaseDate,String baikeUrl,String summary,float weight,long id){
		//参数部分
		StringBuffer sqlBuff = new StringBuffer();
		Hashtable<String,Object> paraHash = new Hashtable<String,Object>();
		
		//sql语句部分
		sqlBuff.append(" update Movie set ");
		sqlBuff.append(" 	name=${name}, ");
		sqlBuff.append(" 	byname=${byname}, ");
		sqlBuff.append(" 	directorId=${directorId}, ");
		sqlBuff.append(" 	areaId=${areaId}, ");
		sqlBuff.append(" 	baikeUrl=${baikeUrl}, ");
		sqlBuff.append(" 	summary=${summary}, ");
		sqlBuff.append(" 	weight=${weight}, ");
		sqlBuff.append(" 	updateTime=now(), ");
		
		//上映时间
		sqlBuff.append(" 	releaseDate= ");		
		if(releaseDate.equals("")){
			sqlBuff.append(" null ");
		}
		else{
			sqlBuff.append(" ${releaseDate} ");
		}
		
		sqlBuff.append(" where id=${id} and ( ");
		sqlBuff.append(" 	name!=${name} ");
		sqlBuff.append(" 	or byname!=${byname} ");
		sqlBuff.append(" 	or directorId!=${directorId} ");
		sqlBuff.append(" 	or areaId!=${areaId} ");
		sqlBuff.append(" 	or baikeUrl!=${baikeUrl} ");
		sqlBuff.append(" 	or summary!=${summary} ");
		sqlBuff.append(" 	or weight!=${weight} ");
		//上映时间
		if(releaseDate.equals("")){
			sqlBuff.append(" or not isNull(releaseDate) ");
		}
		else{
			sqlBuff.append(" or releaseDate!=${releaseDate} or isNull(releaseDate) ");
		}
		sqlBuff.append(" ) ");
		
		//sql参数部分
		paraHash.put("name",name);
		paraHash.put("byname",byname);
		paraHash.put("directorId",directorId);
		paraHash.put("areaId",areaId);
		paraHash.put("releaseDate",releaseDate);
		paraHash.put("baikeUrl",baikeUrl);
		paraHash.put("summary",summary);
		paraHash.put("weight",weight);
		paraHash.put("id",id);
		
		//执行sql 并返回参数
		return dbManager.updateSql(sqlBuff.toString(),paraHash);
	}
	
	/*
	 * 根据ID 删除数据
	 */
	public int deleteData(long id){
		//参数部分
		StringBuffer sqlBuff = new StringBuffer();
		Hashtable<String,Object> paraHash = new Hashtable<String,Object>();
		
		//sql语句部分
		sqlBuff.append(" delete from Movie where id=${id} ");
		
		//sql参数部分
		paraHash.put("id",id);
		
		//执行sql 并返回参数
		return dbManager.updateSql(sqlBuff.toString(),paraHash);
	}
	
	/*
	 * 获得电影数量
	 */
	public long findMovieCount(String logoPath){
		
		//参数部分
		long movieCount = 0;
		StringBuffer sqlBuff = new StringBuffer();
		Hashtable<String,Object> paraHash = new Hashtable<String,Object>();
		
		//sql语句部分
		sqlBuff.append(" select count(id) as movieCount from Movie where logoPath=${logoPath} ");
		
		//sql参数部分
		paraHash.put("logoPath", logoPath);
		
		//执行sql
		movieCount = dbManager.querySqlReturnLong(sqlBuff.toString(), paraHash);
		
		//返回列表
		return movieCount;
	}
	
	/*
	 * 获得电影数量
	 */
	public long findMovieCount(String searchType,String searchKey,long typeId,long areaSortId,String showRow,long areaId){
		
		//参数部分
		long movieCount = 0;
		StringBuffer sqlBuff = new StringBuffer();
		Hashtable<String,Object> paraHash = new Hashtable<String,Object>();
		
		//sql语句部分
		sqlBuff.append(" select count(id) as movieCount from Movie ");
		
		sqlBuff.append(" where ");
		
		//搜索导演名
		if(searchType.equals("director")){
			sqlBuff.append(" Movie.directorId in ( ");
			sqlBuff.append(" 	select id from Director where name like ${searchKey} ");
			sqlBuff.append(" ) ");
			
		}
		//搜索演员名
		else if(searchType.equals("actor")){
			sqlBuff.append(" Movie.id in ( ");
			sqlBuff.append(" 	select movieId from MovieActor ");
			sqlBuff.append(" 	left join Actor on MovieActor.actorId=Actor.id ");
			sqlBuff.append(" 	where name like ${searchKey} ");
			sqlBuff.append(" ) ");
		}
		//搜索角色名
		else if(searchType.equals("role")){
			sqlBuff.append(" Movie.id in( ");
			sqlBuff.append(" 	select movieId from MovieActor ");
			sqlBuff.append(" 	where roleName like ${searchKey} ");
			sqlBuff.append(" ) ");
		}
		//搜索电影名
		else{
			sqlBuff.append(" ( ");
			sqlBuff.append(" 	name like ${searchKey} ");
			sqlBuff.append(" 	or byname like ${searchKey} ");
			sqlBuff.append(" ) ");
		}
		
		//如果选择了 类型
		if(typeId != 0){
			sqlBuff.append(" and id in ( ");
			sqlBuff.append(" 	select movieId from MovieType ");
			sqlBuff.append(" 	where typeId=${typeId} ");
			sqlBuff.append(" ) ");
			
		}
		
		//如果选择了 地区分类
		if(areaSortId != 0){
			//如果选择了 地区
			if(areaId != 0){
				sqlBuff.append(" and areaId=${areaId} ");
			}
			//如果没有选择地区
			else{
				sqlBuff.append(" and areaId in ( ");
				sqlBuff.append(" 	select id from Area ");
				sqlBuff.append(" 	where sortId=${areaSortId} ");
				sqlBuff.append(" ) ");
			}
			
		}
		
		//缺少
		if(showRow.equals("not")){
			sqlBuff.append(" and ( ");
			sqlBuff.append(" logoPath='' ");
			sqlBuff.append(" or baikeUrl='' ");
			sqlBuff.append(" or areaId=0 ");
			sqlBuff.append(" or directorId=0 ");
			sqlBuff.append(" or summary='' ");
			sqlBuff.append(" or isNull(releaseDate) ");
			sqlBuff.append(" ) ");
		}
		//缺少海报
		else if(showRow.equals("notLogo")){
			sqlBuff.append(" and logoPath='' ");
		}
		//缺少百科
		else if(showRow.equals("notBaike")){
			sqlBuff.append(" and baikeUrl='' ");
		}
		//缺少地区
		else if(showRow.equals("notArea")){
			sqlBuff.append(" and areaId=0 ");
		}
		//缺少导演
		else if(showRow.equals("notDirector")){
			sqlBuff.append(" and directorId=0 ");
		}
		//缺少简介
		else if(showRow.equals("notSummary")){
			sqlBuff.append(" and summary='' ");
		}
		//缺少上映日期
		else if(showRow.equals("notReleaseDate")){
			sqlBuff.append(" and isNull(releaseDate) ");
		}
		
		//sql参数部分
		paraHash.put("searchKey", "%"+searchKey+"%");
		paraHash.put("typeId", typeId);
		paraHash.put("areaSortId", areaSortId);
		paraHash.put("areaId", areaId);
		
		//执行sql
		movieCount = dbManager.querySqlReturnLong(sqlBuff.toString(), paraHash);
		
		//返回列表
		return movieCount;
	}
	
	/*
	 * 获得电影列表
	 */
	public List<Movie> findMovieList(String searchType,String searchKey,long typeId,long areaSortId,String showRow,long areaId,String order,boolean asc,long pageNo,long pageSize){
		//参数部分
		List<Movie> movieList = new Vector<Movie>();
		StringBuffer sqlBuff = new StringBuffer();
		Hashtable<String,Object> paraHash = new Hashtable<String,Object>();
		
		//sql语句部分
		sqlBuff.append(" select Movie.*, ");
		sqlBuff.append(" 	(select name from Director where Director.id=Movie.directorId) as directorName, ");
		sqlBuff.append(" 	(select name from Area where Area.id=Movie.areaId) as areaName, ");
		sqlBuff.append(" 	(select count(id) from MovieActor where MovieActor.movieId=Movie.id) as actorCount, ");
		sqlBuff.append(" 	(select count(id) from MovieSource where MovieSource.movieId=Movie.id) as sourceCount, ");
		sqlBuff.append(" 	(select count(id) from MovieType where MovieType.movieId=Movie.id) as typeCount ");
		sqlBuff.append(" from Movie ");
		
		sqlBuff.append(" where ");
		
		//搜索导演名
		if(searchType.equals("director")){
			sqlBuff.append(" Movie.directorId in ( ");
			sqlBuff.append(" 	select id from Director where name like ${searchKey} ");
			sqlBuff.append(" ) ");
			
		}
		//搜索演员名
		else if(searchType.equals("actor")){
			sqlBuff.append(" Movie.id in ( ");
			sqlBuff.append(" 	select movieId from MovieActor ");
			sqlBuff.append(" 	left join Actor on MovieActor.actorId=Actor.id ");
			sqlBuff.append(" 	where name like ${searchKey} ");
			sqlBuff.append(" ) ");
		}
		//搜索角色名
		else if(searchType.equals("role")){
			sqlBuff.append(" Movie.id in( ");
			sqlBuff.append(" 	select movieId from MovieActor ");
			sqlBuff.append(" 	where roleName like ${searchKey} ");
			sqlBuff.append(" ) ");
		}
		//搜索电影名
		else{
			sqlBuff.append(" ( ");
			sqlBuff.append(" 	name like ${searchKey} ");
			sqlBuff.append(" 	or byname like ${searchKey} ");
			sqlBuff.append(" ) ");
		}
		
		//如果选择了 类型
		if(typeId != 0){
			sqlBuff.append(" and id in ( ");
			sqlBuff.append(" 	select movieId from MovieType ");
			sqlBuff.append(" 	where typeId=${typeId} ");
			sqlBuff.append(" ) ");
			
		}
		
		//如果选择了 地区分类
		if(areaSortId != 0){
			//如果选择了 地区
			if(areaId != 0){
				sqlBuff.append(" and areaId=${areaId} ");
			}
			//如果没有选择地区
			else{
				sqlBuff.append(" and areaId in ( ");
				sqlBuff.append(" 	select id from Area ");
				sqlBuff.append(" 	where sortId=${areaSortId} ");
				sqlBuff.append(" ) ");
			}
			
		}
		
		//缺少
		if(showRow.equals("not")){
			sqlBuff.append(" and ( ");
			sqlBuff.append(" logoPath='' ");
			sqlBuff.append(" or baikeUrl='' ");
			sqlBuff.append(" or areaId=0 ");
			sqlBuff.append(" or directorId=0 ");
			sqlBuff.append(" or summary='' ");
			sqlBuff.append(" or isNull(releaseDate) ");
			sqlBuff.append(" ) ");
		}
		//缺少海报
		else if(showRow.equals("notLogo")){
			sqlBuff.append(" and logoPath='' ");
		}
		//缺少百科
		else if(showRow.equals("notBaike")){
			sqlBuff.append(" and baikeUrl='' ");
		}
		//缺少地区
		else if(showRow.equals("notArea")){
			sqlBuff.append(" and areaId=0 ");
		}
		//缺少导演
		else if(showRow.equals("notDirector")){
			sqlBuff.append(" and directorId=0 ");
		}
		//缺少简介
		else if(showRow.equals("notSummary")){
			sqlBuff.append(" and summary='' ");
		}
		//缺少上映日期
		else if(showRow.equals("notReleaseDate")){
			sqlBuff.append(" and isNull(releaseDate) ");
		}
		
		//排序
		sqlBuff.append(" order by ");
		//字段含有中文就转换下
		if(order.equals("name") || order.equals("byname")){
			sqlBuff.append(" convert( ").append(order).append(" using gbk)");
		}
		else{
			sqlBuff.append(order);
		}
		
		//顺序
		if(asc){
			sqlBuff.append(" asc ");
		}
		else{
			sqlBuff.append(" desc ");
		}
		
		sqlBuff.append(" ,id ");
		sqlBuff.append(" limit ${pageNo},${pageSize} ");
		
		//sql参数部分
		paraHash.put("searchKey", "%"+searchKey+"%");
		paraHash.put("typeId", typeId);
		paraHash.put("areaSortId", areaSortId);
		paraHash.put("areaId", areaId);
		paraHash.put("pageNo", (pageNo - 1) * pageSize);
		paraHash.put("pageSize", pageSize);
		
		//执行sql 
		movieList = this.dbManager.querySqlReturnVoList(sqlBuff.toString(), paraHash);
		
		//返回列表
		return movieList;
	}
	
	/*
	 * 获得电影ID
	 */
	public long findMovieId(String name){
		//参数部分
		long movieId = 0;
		StringBuffer sqlBuff = new StringBuffer();
		Hashtable<String,Object> paraHash = new Hashtable<String,Object>();
		
		//sql语句部分
		sqlBuff.append(" select id from Movie where name=${name} ");
		
		//sql参数部分
		paraHash.put("name",name);

		//执行sql
		movieId = dbManager.querySqlReturnLong(sqlBuff.toString(), paraHash);
		
		//返回
		return movieId;
	}
	
	/*
	 * 获得电影对象
	 */
	public Movie findMovie(long id){
		//参数部分
		Movie movie = null;
		StringBuffer sqlBuff = new StringBuffer();
		Hashtable<String,Object> paraHash = new Hashtable<String,Object>();
		
		//sql语句部分
		sqlBuff.append(" select * from Movie where id=${id} ");
		
		//sql参数部分
		paraHash.put("id",id);

		//执行sql
		movie = dbManager.querySqlReturnVo(sqlBuff.toString(), paraHash);
		
		//返回
		return movie;
	}
}
